<#assign object = doc.object>
package ${object.package}.service;

import org.json.JSONObject;
import org.json.JSONException;

import java.io.PrintWriter;
<#if doc["/object/attributes/date[@finder='true']"][0]??>
import java.util.Date;
</#if>
import java.sql.SQLException;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import redora.exceptions.*;
import redora.impl.*;
import ${object.package}.model.*;
import ${object.package}.model.fields.${object.name}Fields;
<#if doc["//enum[@scope='global']"][0]??>
import ${object.package}.model.enums.*;
</#if>
import redora.api.fetch.*;

public class ${object.name}ServiceJSON extends ServiceBase {
	private final Logger l = Logger.getLogger("${object.package}.service.${object.name}ServiceJSON");
	
	private ${object.name}Util util;
	
	public ${object.name}ServiceJSON() throws ConnectException {
		super();
		util = new ${object.name}Util();
	}
	
	/**
	 * @param page (Mandatory) The allowed fetchscope: Scope.Table, Scope.Form, Scope.List.
	 */
	public void findById(Long id, Scope scope, PrintWriter out) throws ConnectException, QueryException, CopyException {
	    if (scope == Scope.Lazy) {
    	  throw new CopyException(new PagingException("Illegal scope for this finder: " + scope));
    	}
    	String SQL;
    	if (scope == Scope.Table)
    		SQL = ${object.name}SQL.FIND_BY_ID_TABLE;
    	else if (scope == Scope.List)
    		SQL = ${object.name}SQL.FIND_BY_ID_LIST;
    	else 
    		SQL = ${object.name}SQL.FIND_BY_ID_FORM;
		try {
			makePreparedStatement(SQL);
			ps.setLong(1, id.longValue());
			rs = ps.executeQuery();
			out.print('{');
			out.print("response:");
			out.print('{');
			out.print("status:0,data:");
			if (rs.next()) {
				util.jsonStream(rs, 0, out, scope);
			}
			out.print('}');
			out.print('}');
			rs.close();
			rs = null;
		} catch(SQLException e) {
			l.log(Level.SEVERE, "Failed to run query " + SQL, e);
			throw new QueryException("Failed to run query: " + SQL, e);
	  	}
	}

	/**
	* Finds records by custom SQL
	* @param page (Mandatory) The allowed fetchscope: Scope.Table, Scope.Form, Scope.List, Scope.Lazy.
	*/
	public void find(String SQL, PrintWriter out, Page page) throws ConnectException, QueryException, CopyException, PagingException {
		if (page.getMode() == Mode.Page) {
            if (page.resultCount() == -1)
            	page.initPageMode(getResultSize(SQL));
            SQL = addPagingForSQL(SQL, page);
        }
		try {
			makePreparedStatement(SQL);
			rs = ps.executeQuery();
			out.print('{');
			out.print("response:");
			out.print('{');
			out.print("status:0,page:{");
			out.print(page.toJSON());
			out.print('}');
			out.print(",data:");
			out.print('[');
			while (rs.next()) {
				if(!rs.isFirst()){
					out.print(',');
				}
				util.jsonStream(rs, 0, out, page.getScope());
			}
			out.print(']');
			out.print('}');
			out.print('}');
			rs.close();
			rs = null;
		} catch(SQLException e) {
			l.log(Level.SEVERE, "Failed to run query: " + SQL, e);
			throw new QueryException("Failed to run query: " + SQL, e);
	  	}
	}
	
	/**
	* Finds All records
	* @param page (Mandatory) The allowed fetchscope: Scope.Table, Scope.List.
	*/
	public void findAll(Page page,PrintWriter out) throws ConnectException, QueryException, CopyException, PagingException {
	    if (!(page.getScope() == Scope.List || page.getScope() == Scope.Table)) {
    	  throw new CopyException(new PagingException("Illegal scope for this finder: " + page.getScope()));
    	}
	    String SQL = page.getScope() == Scope.Table ? ${object.name}SQL.FIND_ALL_TABLE : ${object.name}SQL.FIND_ALL_LIST;
	    if (page.getMode() == Mode.Page) {
            if (page.resultCount() == -1)
            	page.initPageMode(getResultSize(SQL));
            SQL = addPagingForSQL(SQL, page);
        }
		try {
			makePreparedStatement(SQL);
			rs = ps.executeQuery();
			out.print('{');
			out.print("response:");
			out.print('{');
			out.print("status:0,page:{");
			out.print(page.toJSON());
			out.print('}');
			out.print(",data:");
			out.print('[');
			while (rs.next()) {
				if(!rs.isFirst()){
					out.print(',');
				}
				util.jsonStream(rs, 0, out, page.getScope());
			}
			out.print(']');
			out.print('}');
			out.print('}');
			rs.close();
			rs = null;
	  	} catch(SQLException e) {
	    	l.log(Level.SEVERE, "Failed to run query " + SQL, e);
			throw new QueryException("Failed to run query: " + SQL, e);
	  	}
	}
	
<#list object.attributes?children as att>
    <#if (att?node_name == "long" && att.@name?ends_with("Id"))>
    /**
	* @param page (Mandatory) The allowed fetchscope: Scope.Table, Scope.List.
	*/
	public void findBy${att.@fieldName?cap_first}(Long id, Page page, PrintWriter out) throws ConnectException, QueryException, CopyException, PagingException {
		if (!(page.getScope() == Scope.List || page.getScope() == Scope.Table)) {
    	  throw new CopyException(new PagingException("Illegal scope for this finder: " + page.getScope()));
    	}
	    String SQL = page.getScope() == Scope.Table ? ${object.name}SQL.FIND_BY_${att.@fieldName?upper_case}_TABLE : ${object.name}SQL.FIND_BY_${att.@fieldName?upper_case}_LIST;
	    if (page.getMode() == Mode.Page) {
            if (page.resultCount() == -1)
            	page.initPageMode(getResultSize(SQL));
            SQL = addPagingForSQL(SQL, page);
        }
		try {
	    	makePreparedStatement(SQL);
	    	ps.setLong(1, id.longValue());
	    	rs = ps.executeQuery();
	    	out.print('{');
			out.print("response:");
			out.print('{');
			out.print("status:0,page:{");
			out.print(page.toJSON());
			out.print('}');
			out.print(",data:");
			out.print('[');
		    while (rs.next()) {
		      if(!rs.isFirst()){
		        out.print(',');
		      }
		      util.jsonStream(rs, 0, out, page.getScope());
		    }
		    out.print(']');
		    out.print('}');
			out.print('}');
		    rs.close();
		    rs = null;
	  	} catch(SQLException e) {
	    	l.log(Level.SEVERE, "Failed to run query " + SQL, e);
	    	throw new QueryException("Failed to run query: " + SQL, e);
	    }
	}
    </#if>
</#list>

<#list object.attributes.enum as att>
	/**
	* @param page (Mandatory) The allowed fetchscope: Scope.Table, Scope.List.
	*/
	public void findBy${att.@fieldName?cap_first}(<#if att.@scope == "local">${object.name}.</#if>${att.@className} ${att.@fieldName}, Page page, PrintWriter out) throws ConnectException, QueryException, CopyException, PagingException {
		if (!(page.getScope() == Scope.List || page.getScope() == Scope.Table)) {
      		throw new CopyException(new PagingException("Illegal scope for this finder: " + page.getScope()));
    	}
    	String SQL = page.getScope() == Scope.Table ? ${object.name}SQL.FIND_BY_${att.@fieldName?upper_case}_TABLE : ${object.name}SQL.FIND_BY_${att.@fieldName?upper_case}_LIST;
    	if (page.getMode() == Mode.Page) {
            if (page.resultCount() == -1)
            	page.initPageMode(getResultSize(SQL));
            SQL = addPagingForSQL(SQL, page);
        }
    	try {
			makePreparedStatement(SQL);
			ps.setString(1, ${att.@fieldName}.name());
			rs = ps.executeQuery();
			out.print('{');
			out.print("response:");
			out.print('{');
			out.print("status:0,page:{");
			out.print(page.toJSON());
			out.print('}');
			out.print(",data:");
			out.print('[');
			while (rs.next()) {
				if(!rs.isFirst()){
					out.print(',');
				}
				util.jsonStream(rs, 0, out, page.getScope());
			}
			out.print(']');
			out.print('}');
			out.print('}');
			rs.close();
			rs = null;
		} catch(SQLException e) {
			l.log(Level.SEVERE, "Failed to run query " + SQL, e);
			throw new QueryException("Failed to run query: " + SQL, e);
	  	}
	}
</#list>

<#list doc["/object/attributes/*[@finder='true']"] as att>
	/**
	* @param page (Mandatory) The allowed fetchscope: Scope.Table, Scope.Form, Scope.List, Scope.Lazy.
	*/
	public void findBy${att.@fieldName?cap_first}(${att.@className} finder, Page page, PrintWriter out) throws ConnectException, QueryException, CopyException, PagingException {
		if (!(page.getScope() == Scope.List || page.getScope() == Scope.Table)) {
      		throw new CopyException(new PagingException("Illegal scope for this finder: " + page.getScope()));
    	}
    	String SQL = page.getScope() == Scope.Table ? ${object.name}SQL.FIND_BY_${att.@fieldName?upper_case}_TABLE : ${object.name}SQL.FIND_BY_${att.@fieldName?upper_case}_LIST;
    	if (page.getMode() == Mode.Page) {
            if (page.resultCount() == -1)
            	page.initPageMode(getResultSize(SQL));
            SQL = addPagingForSQL(SQL, page);
        }
    	try {
			makePreparedStatement(SQL);
			<#if att?node_name == "long">
			ps.setLong(1, finder.longValue());
			<#elseif att?node_name == "integer">
			ps.setInt(1, finder.intValue());
			<#elseif att?node_name == "string">
			ps.setString(1, finder);
			<#elseif att?node_name == "enum">
			ps.setString(1, finder.name());
			<#elseif att?node_name == "date">
			ps.setDate(1, new java.sql.Date(finder.getTime()));
			<#elseif att?node_name == "boolean">
			ps.setBoolean(1, finder.booleanValue());
			<#elseif att?node_name == "double">
			ps.setDouble(1, finder.doubleValue());
			</#if>
			rs = ps.executeQuery();
			out.print('{');
			out.print("response:");
			out.print('{');
			out.print("status:0,page:{");
			out.print(page.toJSON());
			out.print('}');
			out.print(",data:");
			out.print('[');
			while (rs.next()) {
				if(!rs.isFirst()){
					out.print(',');
				}
				util.jsonStream(rs, 0, out, page.getScope());
			}
			out.print(']');
			out.print('}');
			out.print('}');
			rs.close();
			rs = null;
		} catch(SQLException e) {
			l.log(Level.SEVERE, "Failed to run query " + SQL, e);
			throw new QueryException("Failed to run query: " + SQL, e);
	  	}
	}
</#list>

<#list doc["/object/attributes/set[@multiplicity='n-to-m']"] as att>
  	/**
  	* Finds ${att.@plural?cap_first} based on their (n-to-m) relation with ${att.@class}.
  	* @param page (Mandatory) The allowed fetchscope: Scope.Table, Scope.List.
  	*/
  	public void findBy${att.@theirKey?cap_first} (Long id, Page page, PrintWriter out) throws ConnectException, CopyException, QueryException, PagingException {
    	if (!(page.getScope() == Scope.List || page.getScope() == Scope.Table)) {
      		throw new CopyException(new PagingException("Illegal scope for this finder: " + page.getScope()));
    	}
    	String SQL = page.getScope() == Scope.Table ? ${object.name}SQL.FIND_${object.name?upper_case}_BY_${att.@theirKey?upper_case}_TABLE : ${object.name}SQL.FIND_${object.name?upper_case}_BY_${att.@theirKey?upper_case}_LIST;
    	if (page.getMode() == Mode.Page) {
            if (page.resultCount() == -1)
            	page.initPageMode(getResultSize(SQL));
            SQL = addPagingForSQL(SQL, page);
        }
    	try {
      		makePreparedStatement(SQL);
      		ps.setLong(1, id);
      		rs = ps.executeQuery();
      		out.print('{');
			out.print("response:");
			out.print('{');
			out.print("status:0,page:{");
			out.print(page.toJSON());
			out.print('}');
			out.print(",data:");
			out.print('[');
			while (rs.next()) {
				if(!rs.isFirst()){
					out.print(',');
				}
				util.jsonStream(rs, 0, out, page.getScope());
			}
			out.print(']');
			out.print('}');
			out.print('}');
      		rs.close();
      		rs = null;
    	} catch(SQLException e) {
      		l.log(Level.SEVERE, "Failed to run query " + SQL, e);
      		throw new QueryException("Failed to run query: " + SQL, e);
    	}
  	}
</#list>

<#if object.lazyScope[0]??>
	public void fetchLazy(Long id, PrintWriter out) throws ConnectException, QueryException, CopyException {
		try {
			makePreparedStatement(${object.name}SQL.FETCH_LAZY_BY_ID);
			ps.setLong(1,id);
			rs = ps.executeQuery();
			out.print('{');
			out.print("response:");
			out.print('{');
			out.print("status:0,data:");
			if (rs.next()) 
				util.jsonStream(rs, 0, out, Scope.Lazy);
			out.print('}');
			out.print('}');
			rs.close();
			rs = null;
		} catch(SQLException e) {
			l.log(Level.SEVERE, "Failed to run query " + ${object.name}SQL.FETCH_LAZY_BY_ID, e);
			throw new QueryException("Failed to run query: " + ${object.name}SQL.FETCH_LAZY_BY_ID, e);
	  	}
	}
</#if>
	
	/**
  	* @param page (Mandatory) The allowed fetchscope: Scope.Table, Scope.List, Scope.Form, Scope.Lazy.
  	*/
	public void findByParams(Map<String, String[]> params, Page page, PrintWriter out)
			throws ConnectException, QueryException, CopyException, PagingException {
			//TODO have a look at the implementation for Scope
			String field = page.getScope() == Scope.Table ? ${object.name}SQL.TABLE_FIELDS : ${object.name}SQL.LIST_FIELDS;
			String alias = ${object.name}SQL.ALIAS;
			String SQL = "select " + field + " from ${object.name} as " + alias;
			String whereClause = " where true";
			<#list object.formScope?children as att>
				<#if att?node_type == "element">
			if (params.containsKey("${att.@fieldName}")) {
				whereClause = whereClause + " and ";
					<#if att?node_name == "integer" || att?node_name == "long" || att?node_name == "double" || att?node_name == "boolean">
				whereClause = whereClause + alias + ".${att.@fieldName} = " + params.get("${att.@fieldName}")[0];
					<#else>
				whereClause = whereClause + alias + ".${att.@fieldName} = '" + params.get("${att.@fieldName}")[0] + "'";	
					</#if>
			}	
				</#if>
			</#list>
			<#assign position = 0>
			<#list doc["/object/attributes/set[@multiplicity='n-to-m']"] as att>
			if (params.containsKey("${att.@theirKey}")) {
				SQL = SQL + ", ${att.@relationTableName} as r${position}";
				whereClause = whereClause + " and ";
				whereClause = whereClause + alias + ".id = r${position}.${att.@myKey} and r${position}.${att.@theirKey} = " + params.get("${att.@theirKey}")[0];
			}
			<#assign position = position + 1>
			</#list>
			SQL = SQL + whereClause<#if object.trashcan == "true"> + " and " + alias + ".roDeleted = false"</#if>;
			find(SQL, out, page);
	}

	/**
	 * Create an ${object.name} object from given json string. If the ${object.name} was already
	 * persisted, this ${object.name} is retrieved from persistence before the json string is copied over
	 * the returned object.
	 *  
	 * @param json
	 * @return
	 */
	public ${object.name} fromJson(JSONObject json) throws ConnectException, QueryException, CopyException, LazyException, JSONException {
		${object.name} retVal = new ${object.name}();
		if (!json.isNull(${object.name}Fields.id.name()) && json.getLong(${object.name}Fields.id.name()) > 0) {
			retVal = ServiceFactory.${object.name?uncap_first}Service().findById(json.getLong(${object.name}Fields.id.name()), Scope.Form);
			l.log(Level.INFO, "Retrieved ${object.name} object with id: " + retVal.getId() + " from the database");
		}
		
		util.copy(json, retVal);
		
		return retVal;
	}
}
